\documentclass[12pt]{beamer} 

\usepackage{fontspec}
\ExplSyntaxOn
\let\tl_length:n\tl_count:n
\ExplSyntaxOff

\setsansfont{PT Sans}
\setmonofont{PT Mono}

\usepackage{beamerthemesplit}
\usefonttheme[onlymath]{serif}

\usetheme{CambridgeUS}
\usecolortheme{lily} 

\hypersetup{unicode=true}
\DeclareFontEncoding{T1}
\fontencoding{T1}
\usepackage[russian]{babel}

\usepackage{amsmath,amssymb,amsthm}
\usepackage{graphics,graphicx}
\usepackage{listings}

\usepackage{fancyvrb} 
\usepackage{booktabs} 
\usepackage{colortbl}
\usepackage{multirow}
\usepackage{verbatim}
\usepackage{color}
\usepackage{url}
\usepackage{qrcode}
\usepackage{pifont}


\definecolor{mygreen}{RGB}{28,172,0} % color values Red, Green, Blue
\definecolor{mylilas}{RGB}{170,55,241}
\definecolor{codecolor}{RGB}{0,120,0}
\definecolor{light-gray}{gray}{0.95}
\definecolor{light-green}{RGB}{205,255,204}
\definecolor{light-red}{rgb}{0.9, 0.8, 0.8}
\definecolor{light-yellow}{rgb}{1,1,0.85}
\definecolor{light-blue}{RGB}{255,204,153}
\definecolor{dark-blue}{rgb}{0,0,0.6}
\definecolor{dark-red}{RGB}{204,0,0}
\definecolor{dark-green}{RGB}{28,172,0}

\newcommand{\cmark}{\textcolor{green}{\ding{51}}}
\newcommand{\xmark}{\textcolor{red}{\ding{55}}}

\setbeamercolor{frametitle}{fg=dark-red,bg=gray!10}
\setbeamerfont{title}{series=\bfseries,parent=structure}
\setbeamerfont{frametitle}{series=\bfseries,parent=structure}
\setbeamertemplate{blocks}[rounded][shadow=false]
\setbeamercolor{block title}{fg=structure,bg=white}
\setbeamertemplate{navigation symbols}{}

\renewcommand{\emph}[1]{\textcolor{dark-blue}{#1}}
\newcommand{\emphb}[1]{\textcolor{dark-blue}{\textbf{#1}}}

\usepackage{tikz}
\usetikzlibrary{er,arrows,matrix,positioning,shapes,snakes}
\usetikzlibrary{calc,patterns,decorations.pathmorphing,decorations.markings,positioning}
\usetikzlibrary{arrows,matrix,positioning,decorations,snakes}
\colorlet{myRed}{red!20}
\tikzset{
  rows/.style 2 args={/utils/temp/.style={row ##1/.append style={nodes={#2}}},
    /utils/temp/.list={#1}},
  columns/.style 2 args={/utils/temp/.style={column ##1/.append style={nodes={#2}}},
    /utils/temp/.list={#1}}}
\makeatletter
\tikzset{anchor/.append code=\let\tikz@auto@anchor\relax,
  add font/.code=%
    \expandafter\def\expandafter\tikz@textfont\expandafter{\tikz@textfont#1},
  left delimiter/.style 2 args={append after command={\tikz@delimiter{south east}
    {south west}{every delimiter,every left delimiter,#2}{south}{north}{#1}{.}{\pgf@y}}}}
\makeatother

%\warn{\matcode}[1]{ \textcolor{codecolor}{\texttt{#1}}}
%\newcommand{\matcode}[1]{\begin{Verbatim}[formatcom=\color{red}]#1\end{Verbatim}}
\definecolor{codecolor}{RGB}{0,150,0}
\newcommand{\matcode}[1]{ \textcolor{codecolor}{\texttt{#1}}}


\title{Основы SQL: SELECT}
\subtitle{Базы данных}
\author[Кафедра ММЭ]{Юдинцев В. В.}
\institute[Самарский университет]{КАФЕДРА МАТЕМАТИЧЕСКИХ МЕТОДОВ В ЭКОНОМИКЕ}

%\pgfdeclareimage[height=1cm]{logo}{ssau.pdf}
%\logo{\pgfuseimage{logo}}
%\setbeameroption{show notes}
%\graphicspath{{img/}}

\usepackage{minted}
\usepackage{mdframed}
\surroundwithmdframed{minted}
\mdfsetup{innerleftmargin=0.1cm,innerrightmargin=0.1cm,linecolor=light-gray,linewidth=0,skipabove=1mm,skipbelow=1cm,innerbottommargin=2mm}

\setminted[SQL]{fontsize=\scriptsize,encoding=utf8,breaklines=true}


% frame=lines,framesep=2\fboxsep
\makeatletter
\AtBeginEnvironment{minted}{\dontdofcolorbox}
\def\dontdofcolorbox{\renewcommand\fcolorbox[4][]{##4}}
\makeatother

\lstset{language=SQL,          
    keepspaces=true,
    extendedchars=true,
    basicstyle={\footnotesize  \color{black}},
    breaklines=true,    
    keywordstyle=\color{blue},
    morekeywords=[2]{1}, keywordstyle=[2]{\color{codecolor}},
    identifierstyle={ \bf \color{black} },
    stringstyle=\color{mylilas},
    commentstyle=\color{mygreen},%
    showstringspaces=false,
    numbers=left,
    numberstyle={\tiny \color{gray}},
    numbersep=4pt,   
    frame=single 
}

\makeatother
\setbeamertemplate{footline}
{
  \leavevmode%
  \hbox{%  
  \begin{beamercolorbox}[wd=.3\paperwidth,ht=2.25ex,dp=1ex,center]{author in head/foot}%
    \usebeamerfont{author in head/foot}\insertshortauthor
  \end{beamercolorbox}%  
  \begin{beamercolorbox}[wd=.6\paperwidth,ht=2.25ex,dp=1ex,center]{title in head/foot}%
    \usebeamerfont{title in head/foot}\insertshorttitle
  \end{beamercolorbox}%
  \begin{beamercolorbox}[wd=.1\paperwidth,ht=2.25ex,dp=1ex,center]{date in head/foot}%
  \insertframenumber{} / \inserttotalframenumber\hspace*{1ex}
% \insertframenumber{} 
  \end{beamercolorbox}}%
  \vskip0pt%
}

\setbeamertemplate{headline}{}
%\setbeamertemplate{footline}{1}

\AtBeginSection[]{ 
  \begin{frame}[plain]
  %\vfill  
  \begin{tikzpicture}
  \useasboundingbox (0,0) rectangle(\the\paperwidth,\the\paperheight);  
  \fill[color=dark-red]   (-1cm, 3.9cm) rectangle(\the\paperwidth, 5.9cm);
 %\usebeamerfont{title}\insertsectionhead\par%
  %\node[text width=\the\paperwidth,align=center] at (current page.center) {\color{ExecusharesWhite}\Large\textbf{\insertsectionhead}};  
\node[text width=\the\paperwidth,align=center] at (6cm,4.9cm) {\color{white}\Large\textbf{\insertsectionhead}};
  \end{tikzpicture}
  \end{frame}
}

% 
% Документ
%
%
\begin{document}

{
\setbeamercolor{background canvas}{bg=white} 
\usebackgroundtemplate{\includegraphics[width=\paperwidth,height=\paperheight]{back.png}}
\begin{frame}[plain]
\maketitle
\end{frame}
}

\frame{
\frametitle{Содержание}
\tableofcontents
}

\section{SELECT}

\begin{frame}[fragile]
\frametitle{SELECT}
Оператор запроса в языке SQL, возвращающий набор данных (выборку) из базы данных. 
\vspace{5mm}
\begin{minted}{SQL} 
SELECT 
  <список столбцов>
FROM 
  <список таблиц>
[WHERE <условие выбора строк>]
[GROUP BY <условие группировки>]
[HAVING <условие выбора групп>]
[ORDER BY <условие сортировки>]
\end{minted}
\end{frame} 


\begin{frame}[fragile]
  \frametitle{SELECT}
Все столбцы и строки из таблицы \emph{products} 
\begin{minted}{SQL} 
SELECT * FROM products
\end{minted}
Все строки и выбранные столбцы из таблицы \emph{products} 
\begin{minted}{SQL} 
SELECT product_id, product_name FROM products 
\end{minted}
Выбрать все столбцы из таблицы \emph{products} для \emph{product\_id} = 152
\begin{minted}{SQL} 
SELECT * FROM products WHERE product_id = 152
\end{minted}
\end{frame} 

\begin{frame}[fragile]
\frametitle{SELECT}
Дополнительные условия
\begin{minted}{SQL} 
SELECT 
  product_id, product_name 
FROM 
  products 
WHERE 
  product_id = 152 AND manufacturer_id = 12
\end{minted}

Лоические операции с условиями 
\begin{itemize}
  \item AND
  \item OR 
  \item NOT
\end{itemize}
\end{frame} 

\begin{frame}
\frametitle{Реляционные операторы}
\begin{itemize}
  \item[= ] Равно
  \item[> ] Больше
  \item[< ] Меньше
  \item[>=] Больше или равно
  \item[<=] Меньше или равно
  \item[!=] Не равно 
\end{itemize}
\end{frame}

\begin{frame}[fragile]
\frametitle{Пример}
Исходная таблица
\begin{minted}{SQL}  
|store_id|store_name|
|--------|----------|
|       1|Московский|
|       2|Чкаловский|
|       3|Петровский|
|       4|Алабинский|
|       5|Филиал_А  |
\end{minted}
Все строки с идентификатором \emph{store\_id} больше 2 и меньше 5
\begin{minted}{SQL}  
select * from stores where store_id > 2 AND store_id < 5;
\end{minted}
Результат
\begin{minted}{SQL}  
|store_id|store_name|
|--------|----------|
|       3|Петровский|
|       4|Алабинский|
\end{minted}
\end{frame}



\begin{frame}[fragile,c]
\frametitle{BETWEEN}
\begin{itemize}
  \item Для выбора значений из интервала можно использовать оператор \emphb{BETWEEN}
  \item Все строки таблицы с идентификатором \emph{store\_id} в интервале от 2 до 5 \\
\begin{minted}{SQL}  
select * from stores where store_id BETWEEN 2 AND 4;
\end{minted}
Результат
\begin{minted}{SQL}  
|store_id|store_name|
|--------|----------|
|       2|Чкаловский|
|       3|Петровский|
|       4|Алабинский|
\end{minted}    
\end{itemize}
\end{frame}

\begin{frame}[fragile,c]
\frametitle{NOT}
\begin{itemize}
  \item Для отрицания условия используется оператор \alert{NOT}
  \item Все строки таблицы с идентификатором \emph{store\_id} \alert{не} в интервале от 2 до 5 \\
\begin{minted}{SQL}  
select * from stores where store_id NOT BETWEEN 2 AND 4;
\end{minted}
Результат
\begin{minted}{SQL}  
|store_id|store_name|
|--------|----------|
|       1|Московский|
|       5|Филиал_А  |
\end{minted}
\end{itemize}
\end{frame}

\begin{frame}[fragile,c]
\frametitle{DISTINCT}
Директива \emphb{DISTINCT} используется с \emphb{SELECT} для выбора только отличающихся значений. 

Например, таблица студентов содержит столбец с датой рождения:
\begin{minted}{SQL}  
id|name      |birthdate |
--|----------|----------|
 1|Поповский |2000-10-20|
 2|Аничков   |2001-12-05|
 3|Вениаминов|2001-07-12|
 4|Чеботарев |2001-02-10|  
\end{minted}  
Необходимо вывести список \emph{годов рождения} студентов без повторений, т.е. результатом должен быть список 2000, 2001. 
\end{frame}

\begin{frame}[fragile,c]
\frametitle{DISTINCT}
При использовании простого \emph{SELECT} и функции \emph{YEAR} для извлечения номера года из даты результатом будет список годов рождения с повторениями
\begin{minted}{SQL} 
select YEAR(birthdate) as birthyear from student;  

birthyear|
---------|
     2000|
     2001|
     2001|
     2001|
\end{minted}  
Чтобы исключить повторения необходимо использовать директиву \emphb{DISTINCT}
\begin{minted}{SQL} 
select DISTINCT YEAR(birthdate) as birthyear from student;  

birthyear|
---------|
     2000|
     2001|
\end{minted}  
\end{frame}


\begin{frame}[fragile]
\frametitle{Функции для работы с датами}
{\scriptsize \url{https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html} }
\begin{itemize}
  \item MONTH()
  \item YEAR()
  \item DAY()
  \item NOW()
  \item WEEKDAY()
  \item QUARTER()
  \item DATE\_ADD()
  \item DATEDIFF()
  \item ...
\end{itemize}
\end{frame}


\begin{frame}[fragile]
\frametitle{Сравнение строк}
Для сравнения строк используются операторы 
\begin{itemize}
  \item LIKE
  \item NOT LIKE
  \item STRCMP
  \item MATCH -- полнотекстовый поиск для больших объёмов текста
\end{itemize}
\end{frame}

\begin{frame}[fragile]
\frametitle{Оператор LIKE}
Таблица \emph{stores}
\begin{minted}{SQL}  
|store_id|store_name|
|--------|----------|
|       1|Московский|
|       2|Чкаловский|
|       3|Петровский|
|       4|Алабинский|
\end{minted}
Выбор строк с именами, содержащими сочетание \emph{'вск'};
\begin{minted}{SQL}  
select * from stores where store_name LIKE '%вск%';
\end{minted}
\begin{minted}{SQL}  
|store_id|store_name|
|--------|----------|
|       1|Московский|
|       2|Чкаловский|
|       3|Петровский|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Шаблоны LIKE}
\begin{itemize}
  \item \mintinline[fontsize=\footnotesize]{SQL}|LIKE '%вск%'| \\
  \% -- любое количество символов (или отсутствие символов): 'Чкало\alert{вск}ий', '\alert{вск}ряк!', 'Иже\alert{вск}', '\alert{вск}'
  \item \mintinline[fontsize=\footnotesize]{SQL}|LIKE '_ом'| \\ \_ -- только один любой символ: 'c\alert{ом}', 'д\alert{ом}', но не 'обл\alert{ом}' 
  \item Чтобы использовать эти символы в строках для сравнения именно как символы, а не как шаблоны их нужно \emph{экранировать} символом \textbackslash \\
  \mintinline[fontsize=\footnotesize]{SQL}|LIKE '%\_ом'|
\end{itemize}
\end{frame}

\begin{frame}[fragile]
\frametitle{Пример LIKE}
Выбор из таблицы
\begin{minted}{SQL}  
|store_id|store_name|
|--------|----------|
|       1|Московский|
|       2|Чкаловский|
|       3|Петровский|
|       4|Алабинский|  
\end{minted}
строк с именами, заканчивающимися на \emph{'нский'}:
\begin{minted}{SQL}  
select * from stores where store_name LIKE('%нский');  
  
|store_id|store_name|
|--------|----------|
|       4|Алабинский|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Оператор NOT LIKE}
Таблица \emph{stores}
\begin{minted}{SQL}  
|store_id|store_name|
|--------|----------|
|       1|Московский|
|       2|Чкаловский|
|       3|Петровский|
|       4|Алабинский|
\end{minted}
Выбор строк с именами, \alert{НЕ} содержащими сочетание \emph{'вск'};
\begin{minted}{SQL}  
select * from stores where store_name NOT LIKE '%вск%';

|store_id|store_name|
|--------|----------|
|       4|Алабинский|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{STRCMP}
Результат функции \mintinline[fontsize=\footnotesize]{SQL}|STRCMP(str1,str2)|
\begin{itemize}
  \item[-1] str1 меньше str2
  \item[0 ] str1 и str2 равны
  \item[+1] str1 больше str2
\end{itemize}
\end{frame}  

\begin{frame}[fragile]
\frametitle{Примеры STRCMP}
Строка \emph{ТекстБ} больше строки \emph{ТекстА}
\begin{minted}{SQL}  
select STRCMP('ТекстА','ТекстБ');

|STRCMP('ТекстА','ТекстБ')|
|-------------------------|
|                       -1|
\end{minted}
Строка \emph{ТекстА} больше строки \emph{Текст}
\begin{minted}{SQL}  
select STRCMP('ТекстА','Текст');
  
|STRCMP('ТекстА', 'Текст')|
|-------------------------|
|                        1|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{STRCMP и регистр букв}
Строки сравниваются без учёта регистра
\begin{minted}{SQL}  
select STRCMP('ТЕКСТ','Текст');
  
|STRCMP('ТЕКСТ', 'Текст')|
|------------------------|
|                       0|
\end{minted}
\begin{minted}{SQL}  
select * from viewer where FNAME LIKE 'а%';

|id|FNAME  |LNAME |MNAME     |gender|birthdate |
|--|-------|------|----------|------|----------|
| 1|Андрей |Петров|Викторович|М     |1981-11-21|
\end{minted}
Это поведение зависит от типа поля в котором хранятся строки.
\end{frame}

\section{Соединение таблиц}

\begin{frame}
\frametitle{Пример базы данных}
\begin{center}
\includegraphics[width=0.95\linewidth]{teachers2.png}
\end{center}
\end{frame}


\begin{frame}[fragile]
\frametitle{UNION}
Объединение (\alert{UNION}) таблиц
\begin{minted}{SQL}  
SELECT id, name from student 
UNION
SELECT id, name from teacher;
\end{minted}  
Преподаватели и студенты
\begin{minted}{SQL}  
  id|name      |
  --|----------|
   1|Поповский | 
   2|Аничков   |
   3|Вениаминов|
   4|Чеботарев |
   4|Баженов   |
   5|Афонин    |
   6|Барсов    |
\end{minted}  
Количество столбцов должно быть одинаково. Имена столбцов определяются первой таблицей в запросе.
\end{frame}

\begin{frame}
\frametitle{INNER JOIN}
\begin{itemize}
  \item Оператор \emphb{INNER JOIN} формирует таблицу из записей двух или нескольких таблиц 
  \item Каждая строка из первой (левой) таблицы, сопоставляется с каждой строкой из второй (правой) таблицы 
  \item Если условие \emphb{ON} для строки истинно, то строка попадают в результирующую таблицу
\end{itemize}
\end{frame}

\begin{frame}[fragile]
\frametitle{INNER JOIN}
Список студентов, у которых есть руководители  
\begin{minted}{SQL}  
SELECT 
  student.name, teacher.name as superviser_name
FROM 
  student
INNER JOIN teacher ON teacher.id = student.superviser_id
\end{minted} 
Результат 
\begin{minted}{SQL}
name     |superviser_name|
---------|---------------|
Поповский|Баженов        |
Аничков  |Барсов         |
\end{minted}  
\end{frame}

\begin{frame}[fragile]
\frametitle{INNER JOIN}
При отсутствии условия \emphb{ON} спиcок будет большой (все сочетания строк)
\begin{minted}{SQL}  
name        superviser_name
----------  -----------------
Поповский   Баженов
Аничков     Баженов
Вениаминов  Баженов
Чеботарев   Баженов
Поповский   Афонин
Аничков     Афонин
Вениаминов  Афонин
Чеботарев   Афонин
Поповский   Барсов
Аничков     Барсов
...
Вениаминов  Тихонов
Чеботарев   Тихонов
Поповский   Дмитриев
Аничков     Дмитриев
Вениаминов  Дмитриев
Чеботарев   Дмитриев
\end{minted}  
\end{frame}


\begin{frame}[fragile]
\frametitle{Запрос к двум табицам}
Список студентов, у которых есть руководители, можно получить запросом к двум таблицам (\emph{FROM}) с условием \emph{WHERE}:
\begin{minted}{SQL}  
SELECT 
  student.name, teacher.name as superviser_name
FROM 
  student, tecaher
WHERE 
  teacher.id = student.superviser_id
\end{minted} 
Результат 
\begin{minted}{SQL}
name     |superviser_name|
---------|---------------|
Поповский|Баженов        |
Аничков  |Барсов         |
\end{minted}  
Вариант c \emph{INNER JOIN} предпочтительней
\end{frame}


\begin{frame}[fragile]
\frametitle{Запрос к трём таблицам}
Студенты, у которые сдали хотя бы один экзамен и есть руководитель
\begin{minted}{SQL}
SELECT 
  student.name, exam.exam_id, exam.mark, teacher.name 
FROM 
  student
INNER JOIN exam ON exam.student_id = student.id
INNER JOIN teacher ON teacher.id = student.superviser_id
\end{minted} 
\begin{minted}{SQL}
name         exam_id    mark  name
---------  ---------  ------  -------
Поповский         10       5  Баженов
Поповский         20       4  Баженов
Аничков           10       3  Барсов
Аничков           30       4  Барсов
\end{minted} 
\end{frame}


\begin{frame}[fragile]
\frametitle{Запрос к трём таблицам}
Тот же результат без использования \emphb{INNER JOIN}
\begin{minted}{SQL}  
SELECT 
  student.name, exam.exam_id, exam.mark, teacher.name 
FROM 
  student, exam, teacher
WHERE 
  exam.student_id = student.id
AND
  teacher.id = student.superviser_id
\end{minted}
\begin{minted}{SQL}
name         exam_id    mark  name
---------  ---------  ------  -------
Поповский         10       5  Баженов
Поповский         20       4  Баженов
Аничков           10       3  Барсов
Аничков           30       4  Барсов
\end{minted}   
\end{frame}

\begin{frame}[fragile]
\frametitle{Псевдонимы имён таблиц}
В директиве \emph{FROM} после имени таблицы может быть задан псевдоним таблицы. Например, можно дать таблицам короткие имена:
\begin{minted}{SQL}  
SELECT 
  s.name, e.exam_id, e.mark, t.name as teacher_name
FROM 
  student s, exam e, teacher t
WHERE 
  e.student_id = s.id
AND
  t.id = s.superviser_id;
\end{minted}  
Показанные здесь псевдонимы, сотоящие из одной буквы, усложняют чтение запроса и приведены только для примера.  
\end{frame}

\begin{frame}[fragile]
\frametitle{LEFT JOIN}
\begin{minted}{SQL}  
SELECT
  a, b
FROM
  A
LEFT JOIN B 
  ON A.f = B.f
\end{minted}
\begin{itemize}
  \item Строки из A (левая таблица), которым соответсвуют строки в таблице В
  \item Строки из A и строки из таблицы B, заполненные значениями NULL, если строкам из таблицы А не соответсвуют знаечния из таблицы В (условие \alert{ON} не выпоняется).
\end{itemize}
\end{frame}

\begin{frame}[fragile]
\frametitle{LEFT JOIN}
Левая таблица -- \emph{teacher}, правая -- \emph{position}
\begin{minted}{SQL} 
SELECT 
 id, teacher.name, position.name as position_name 
FROM 
 teacher
LEFT JOIN position ON
 teacher.position_id = position.position_id
\end{minted}
Для Барсова отсутствует запись в таблице \emph{position} (правая таблица запроса), поэтому \emph{position\_name} для этой записи равно пустое (\emph{NULL})
\begin{minted}{SQL}  
id|name    |position_name|
--|--------|-------------|
 4|Баженов |Профессор    |
 7|Тихонов |Профессор    |
 5|Афонин  |Доцент       |
 8|Дмитриев|Доцент       |
 6|Барсов  |             |
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{LEFT JOIN}
Если поменять порядок таблиц
\begin{minted}{SQL} 
SELECT 
  id, teacher.name, position.name as position_name 
FROM 
  position
LEFT JOIN
  teacher ON teacher.position_id = position.position_id
\end{minted}
то результатом будет список только тех преподавателей, у которых есть запись о должности:
\begin{minted}{SQL}  
id|name    |position_name|
--|--------|-------------|
 4|Баженов |Профессор    |
 7|Тихонов |Профессор    |
 5|Афонин  |Доцент       |
 8|Дмитриев|Доцент       |
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{RIGHT JOIN}
Тот же результат получится, если в исходном запросе использовать \emph{RIGHT JOIN}
\begin{minted}{SQL} 
SELECT 
 id, teacher.name, position.name as position_name 
FROM 
 teacher
RIGHT JOIN position ON
 teacher.position_id = position.position_id
\end{minted}
Результатом будет список только тех преподавателей, у которых есть запись о должности:
\begin{minted}{SQL}  
id|name    |position_name|
--|--------|-------------|
 4|Баженов |Профессор    |
 7|Тихонов |Профессор    |
 5|Афонин  |Доцент       | 
 8|Дмитриев|Доцент       |
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Соединения и операции с множествами}
Пересечение
\begin{minted}{SQL} 
 A.id        B.id
-------     -------
   1           3 
   2           4
   3           5
   4           7
   5           8
\end{minted}
\includegraphics[trim=20 430 60 150,clip,width=1.0\linewidth]{JOIN-SET.png}  
\end{frame}


\begin{frame}[fragile]
\frametitle{Соединения и операции с множествами}
Пересечение
\begin{minted}{SQL} 
  A.id        B.id
 -------     -------
    1           3 
    2           4
    3           5
    4           7
    5           8
\end{minted}
\includegraphics[trim=16 310 60 250,clip,width=1.0\linewidth]{JOIN-SET.png}  
\end{frame}

\begin{frame}[fragile]
  \frametitle{Соединения и операции с множествами}
  \begin{minted}{SQL} 
    A.id        B.id
   -------     -------
      1           3 
      2           4
      3           5
      4           7
      5           8
  \end{minted}
  \includegraphics[trim=16 180 60 400,clip,width=1.0\linewidth]{JOIN-SET.png}  
  \end{frame}
  
  \begin{frame}[fragile]
    \frametitle{Соединения и операции с множествами}
    Разность
    \begin{minted}{SQL} 
      A.id        B.id
     -------     -------
        1           3 
        2           4
        3           5
        4           7
        5           8
    \end{minted}
    \includegraphics[trim=16 20 60 550,clip,width=1.0\linewidth]{JOIN-SET.png}  
    \end{frame}

\begin{frame}[fragile]
\frametitle{Еще один пример}
Информационная система склада  
\vfill
\includegraphics[width=1\linewidth]{store-products-scheme.png}  
\end{frame}

\begin{frame}[fragile]
\frametitle{SQL DDL}
\begin{columns}
\column{0.5\linewidth}
\begin{minted}{SQL} 
CREATE TABLE STORES
(
  STORE_ID SERIAL PRIMARY KEY,
  NAME VARCHAR(100)
);
\end{minted}
\begin{minted}{SQL} 
CREATE TABLE PRODUCTS
(
  PRODUCT_ID SERIAL PRIMARY KEY,
  NAME VARCHAR(100),
  PRICE NUMERIC(8,2),
  STORE_ID BIGINT UNSIGNED,
  FOREIGN KEY (STORE_ID) REFERENCES STORES(STORE_ID) 
);
\end{minted}
\column{0.5\linewidth}
\includegraphics[width=0.7\linewidth]{stores-products-sql.png}  
\end{columns}
\end{frame}

\begin{frame}[fragile]
  \frametitle{Заполняем данными?}
  \begin{minted}{SQL} 
    INSERT INTO STORES (NAME) VALUES 
    ('Космопорт'),
    ('Аврора'),
    ('Империя');
    
    INSERT INTO PRODUCTS (STORE_ID, NAME, PRICE) VALUES 
    ('Космопорт','Чайник',800.50),
    ('Космопорт','Телевизор',15640),
    ('Аврора','Принтер',10000),
    ('Империя','Ноутбук',50000);
\end{minted}
\end{frame}
  
  

\begin{frame}[fragile]
\frametitle{Заполняем данными}
\begin{minted}{SQL} 
INSERT INTO STORES (NAME) VALUES 
('Космопорт'),
('Аврора'),
('Империя');

INSERT INTO PRODUCTS (STORE_ID, NAME, PRICE) VALUES 
(
 (SELECT STORE_ID FROM STORES WHERE NAME='Космопорт'),
 'Чайник',800.50),
(
 (SELECT STORE_ID FROM STORES WHERE NAME='Космопорт'),
 'Телевизор',15640 
),
( 
 (SELECT STORE_ID FROM STORES WHERE NAME='Аврора'),
 'Принтер',10000
),
( (SELECT STORE_ID FROM STORES WHERE NAME='Империя'),
 'Ноутбук',50000
);
\end{minted}
\end{frame}



\begin{frame}[fragile]
\frametitle{Товары в филиале}
\begin{minted}{SQL} 
SELECT 
  STORES.name,
  PRODUCTS.name,
  PRODUCTS.price
FROM 
  PRODUCTS LEFT JOIN STORES ON PRODUCTS.STORE_ID = STORES.STORE_ID
WHERE
  STORES.NAME = 'Космопорт'; 


name     |name     |price   |
---------+---------+--------+
Космопорт|Чайник   |  800.50|
Космопорт|Телевизор|15640.00|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Товары, которых нет в филиале}
\begin{minted}{SQL} 
SELECT 
  STORES.name,
  PRODUCTS.name,
  PRODUCTS.price
FROM 
  PRODUCTS LEFT JOIN STORES ON PRODUCTS.STORE_ID = STORES.STORE_ID
WHERE
  NOT STORES.NAME = 'Аврора';

 name     |name     |price   |
 ---------+---------+--------+
 Космопорт|Чайник   |  800.50|
 Космопорт|Телевизор|15640.00|
 Империя  |Ноутбук  |50000.00|
\end{minted}
\end{frame}
 

\section{GROUP BY, HAVING и агрегатные функции}

\begin{frame}
\frametitle{Пример}
К таблицам (учителя, студенты, должности) добавим таблицу экзамены, которая содержит
\begin{itemize}
  \item Идентификатор экзамена -- \emphb{exam\_id}
  \item Ссылку на студента, сдавшего экзамен -- \emphb{student\_id}
  \item Оценку -- \emphb{mark}
\end{itemize}  
\end{frame}
  

\begin{frame}
\frametitle{Пример}
\includegraphics[width=0.95\linewidth]{teachers2.png}
\end{frame}


\begin{frame}[fragile]
\frametitle{Студенты с оценками}
Выбор из таблицы только тех, студентов, которые сдали хотя бы один экзамен:
\begin{minted}{SQL} 
SELECT 
  student.name, exam.exam_id, exam.mark 
FROM 
  student
LEFT JOIN exam ON
  student.id = exam.student_id 
WHERE 
  exam.exam_id IS not null;
\end{minted}
Результат выполнения запроса
\begin{minted}{SQL} 
name      |exam_id|mark|
----------|-------|----|
Поповский |     10|   5|
Поповский |     20|   4|
Аничков   |     10|   3|
Вениаминов|     10|   4|
Аничков   |     30|   4|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Агрегатные функции}
\begin{itemize}
  \item В первом столбце результата несколько раз повторяются имена студентов, которые сдали больше одного экзамена 
  \item Можно \alert{сгруппировать} эти повторяющиеся значения имени, но тогда к остальным полям нужно применить некоторые \emph{агрегирующие} функции, чтобы превратить несколько занчений в одно, например найти среднее значение полученных оценок, максимальное значение, минимальное.  
  \item Для таких запросов используется оператор \emph{GROUP BY} и \emph{агрегатные функции}.
\end{itemize}
\begin{minted}{SQL} 
name      |exam_id|mark|
----------|-------|----|
Поповский |     10|   5|
Поповский |     20|   4|
Аничков   |     10|   3|
Вениаминов|     10|   4|
Аничков   |     30|   4|
\end{minted}
\end{frame}

\begin{frame}
\frametitle{GROUP BY}
  \begin{itemize}
    \item \emphb{GROUP BY} используется для объединения результатов выборки по одному или нескольким столбцам
    \item \emphb{GROUP BY} используется совместно с агрегатными функциями
  \end{itemize}  
\end{frame}

\begin{frame}
  \frametitle{Агрегатные функции}
  \emph{Агрегатной функцией} в языке \emph{SQL} называется функция, возвращающая какое-либо одно \emph{значение по набору значений столбца}:
  \begin{itemize}
    \item \emph{COUNT}
    \item \emph{SUM}
    \item \emph{AVG}
    \item \emph{MAX}
    \item \emph{MIN}
  \end{itemize}  
\end{frame}

\begin{frame}[fragile]
\frametitle{AVG()}
Студенты, которые сдали хотя бы один экзамен c вычислением среднего балла полученных оценок:
\begin{minted}{SQL} 
SELECT 
  student.name, AVG(exam.mark) as avg_mark
FROM 
  student
LEFT JOIN exam ON
  student.id = exam.student_id 
WHERE 
  exam.exam_id IS not null
GROUP BY
  student.name;
\end{minted}
Результат выполнения запроса
\begin{minted}{SQL} 
name      |avg_mark|
----------|--------|
Аничков   |  3.5000|
Вениаминов|  4.0000|
Поповский |  4.5000|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{MIN() и MAX()}
Те же студенты с выводом минимальной и максимальной оценки:
\begin{minted}{SQL} 
SELECT 
  student.name, 
  MIN(exam.mark) as min_mark, MAX(exam.mark) as max_mark
FROM 
  student
LEFT JOIN exam ON student.id = exam.student_id 
WHERE 
  exam.exam_id IS not null
GROUP BY
  student.name;
\end{minted}
Результат выполнения запроса
\begin{minted}{SQL} 
name      |min_mark|max_mark|
----------|--------|--------|
Аничков   |       3|       4|
Вениаминов|       4|       4|
Поповский |       4|       5|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{SUM()}
Суммарное количество баллов за все экзамены у студента. Из запроса исключаем условие \emph{WHERE}, чтобы увидеть и тех студентов, которые ещё ничего не сдали
\begin{minted}{SQL} 
SELECT 
  student.name, 
  SUM(exam.mark) as sum_marks
FROM 
  student
LEFT JOIN exam ON 
  student.id = exam.student_id 
GROUP BY
  student.name;
\end{minted}
Результат выполнения запроса
\begin{minted}{SQL} 
name      |sum_marks|
----------|---------|
Аничков   |        7|
Вениаминов|        4|
Поповский |        9|
Чеботарев |         |
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{COUNT()}
При помощи функции \emph{COUNT} можно определить количество экзаменов, которое сдал каждый студент. 
\begin{minted}{SQL} 
SELECT 
  student.name, COUNT(exam.mark) as n_exams
FROM 
  student
LEFT JOIN exam ON
  student.id = exam.student_id 
GROUP BY
  student.name;  
\end{minted}
Результат выполнения запроса
\begin{minted}{SQL} 
name      |n_exams|
----------|-------|
Аничков   |      2|
Вениаминов|      1|
Поповский |      2|
Чеботарев |      0|
\end{minted}
\end{frame}


\begin{frame}[fragile]
\frametitle{Сортировка}
Результат можно отсортировать по заданному столбцу при помощи оператора \emph{ORDER BY}
\begin{minted}{SQL} 
SELECT 
  student.name, COUNT(exam.mark) as n_exams
FROM 
  student
LEFT JOIN exam ON
  student.id = exam.student_id 
GROUP BY
  student.name
ORDER BY n_exams;  
\end{minted}
По умолчанию сортировка выполняется по возрастанию указанного поля:
\begin{minted}{SQL} 
name      |n_exams|
----------|-------|
Чеботарев |      0|
Вениаминов|      1|
Поповский |      2|
Аничков   |      2|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Сортировка}
\emph{ORDER BY} имя\_поля \emph{DESC} используется для сортировки по убыванию:
\begin{minted}{SQL} 
SELECT 
  student.name, COUNT(exam.mark) as n_exams
FROM 
  student
LEFT JOIN exam ON
  student.id = exam.student_id 
GROUP BY
  student.name
ORDER BY n_exams DESC;  
\end{minted}
По умолчанию сортировка выполняется по возрастанию указанного поля:
\begin{minted}{SQL} 
name      |n_exams|
----------|-------|
Поповский |      2|
Аничков   |      2|
Вениаминов|      1|
Чеботарев |      0|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Сортировка по нескольким полям}
Сортировка может быть выполнена по нескольким столбцам (полям):
\begin{minted}{SQL} 
SELECT 
  student.name, COUNT(exam.mark) as n_exams
FROM 
  student
LEFT JOIN exam ON
  student.id = exam.student_id 
GROUP BY
  student.name
ORDER BY n_exams DESC, student.name ASC;  
\end{minted}
По количеству сданных экзаменов, а затем по имени:
\begin{minted}{SQL} 
name      |n_exams|
----------|-------|
Аничков   |      2|
Поповский |      2|
Вениаминов|      1|
Чеботарев |      0|
\end{minted}
\end{frame}


\begin{frame}[fragile]
\frametitle{HAVING}
Выражение \emphb{HAVING} позволяет отобрать по заданному условию результаты агрегатных функций. Студенты с суммой баллов больше 6:
\begin{minted}{SQL} 
SELECT 
  student.name, 
  SUM(exam.mark) as sum_marks
FROM 
  student
LEFT JOIN exam ON 
  student.id = exam.student_id 
GROUP BY
  student.name
HAVING sum_marks>6;
\end{minted}
Результат выполнения запроса
\begin{minted}{SQL} 
name     |sum_marks|
---------|---------|
Аничков  |        7|
Поповский|        9|
\end{minted}
\end{frame}

\section{Ограничение вывода}

\begin{frame}[fragile]
\frametitle{LIMIT}
\begin{itemize}
  \item Для ограничения количество получаемых в результате запроса строк используется директива \emph{LIMIT} в конце запроса. Первые десять строк: \\
\begin{minted}{SQL} 
SELECT * FROM users ORDER BY id LIMIT 10
\end{minted}
  \item Десять строк с пропуском первых 5 
\begin{minted}{SQL} 
SELECT * FROM users ORDER BY id LIMIT 5, 10
\end{minted}
\item Десять строк с пропуском первых 5 
\begin{minted}{SQL} 
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 5
\end{minted}
\end{itemize}
\end{frame}

\begin{frame}[fragile,c]
\frametitle{LIMIT}
\emph{Первые два студента} с максимальным средним баллом:  
\begin{minted}{SQL} 
SELECT 
  student.name, AVG(exam.mark) as avg_mark
FROM 
  student
LEFT JOIN exam ON
  student.id = exam.student_id 
WHERE 
  exam.exam_id IS not null
GROUP BY
  student.name
ORDER BY avg_mark DESC 
LIMIT 2;
\end{minted}
Результат выполнения запроса:
\begin{minted}{SQL} 
name      |avg_mark|
----------|--------|
Поповский |  4.5000|
Вениаминов|  4.0000|
\end{minted}
\end{frame}

\section{Подзапросы} 

\begin{frame}[fragile,c]
\frametitle{Подзапросы}
\begin{itemize}
  \item SQL позволяет создавать вложенные запросы
  \item Вложенные запросы используются для вставки значения в таблицу, которое можно найти по другому значению атрибута
  \item Вложенные запросы позволяют вычислить значение, которое затем используется в объемлющем (верхнем) запросе
\end{itemize}
\begin{minted}{SQL} 
SELECT 
 name as student_name 
FROM 
 student
WHERE 
 superviser_id = (SELECT id FROM teacher WHERE name = 'Барсов');
\end{minted}
\end{frame}


\begin{frame}[fragile,c]
\frametitle{Подзапросы}
Имя студента, руководителем выпускной работы которого является преподаватель Барсов. 

Подзапрос вычисляет идентификатор (\emph{id}) преподавателя по его имени и полученное значение используется запросом верхнего уровня для поиска студента в таблице \emph{student} с полем \emph{superviser\_id}, равным идентификатору Барсова:
\begin{minted}{SQL} 
SELECT 
 name as student_name 
FROM 
 student
WHERE 
 superviser_id = (SELECT id FROM teacher WHERE name = 'Барсов');
\end{minted}
\end{frame}

\frame[plain]{
\frametitle{Список использованных источников}
\small
\begin{itemize}
  \item Хомоненко А. Д., Цыганков В. М., Мальцев М. Г. Базы данных: Учебник для высших учебных заведений / Под ред. npоф. А. Д. Хомоненко. -- 6-е изд., доп. - СПб.: КОРОНА-Век, 2009. -- 736 с.
  \item Осипов Д. Л. Технологии проектирования баз данных. -- М.: ДМК Пресс, 2019.  
  \item SQL Учебник \\
  {\scriptsize \url{https://schoolsw3.com/sql/index.php}}  
  \item SQL Tutorial \\
  {\scriptsize \url{https://www.tutorialspoint.com/sql/index.htm}}  
\end{itemize} 
}

\frame[plain,c]{
  \begin{center}
  \qrcode[height=1in]{https://classmech.ru/pages/databases/}\\
  \vspace{2mm}
  {\tiny \url{https://classmech.ru/pages/databases/} }
  \end{center}
}



\end{document}